from snake import Food
from snake import SnakeNode
import pymysql

# 数据库---食物 和 蛇的一个节点
class Food_and_snakenode:

    def __init__(self, db, user, passwd, host='116.63.136.129', port=3306, charset='utf8'):
        """
        数据库配置
        :param db:              数据库名字
        :param user:            链接的用户名
        :param passwd:          链接的密码
        :param host:            IP地址默认是：127.0.0.1  localhost
        :param port:            端口默认：3306 可修改
        :param charset:         默认转码：utf8
        """
        self.__db = db
        self.__user = user
        self.__passwd = passwd
        self.__host = host
        self.__port = port
        self.__charset = charset
        self.__connect = None
        self.__cursor = None

    # 连接数据库
    def connect_db(self):
        """
        dbManager._connect_db()
        连接数据库
        :return:
        """
        params = {
            "db": self.__db,
            "user": self.__user,
            "passwd": self.__passwd,
            "host": self.__host,
            "port": self.__port,
            "charset": self.__charset
        }
        self.__connect = pymysql.connect(**params)
        self.__cursor = self.__connect.cursor()
        # print('连接数据库成功')

    # 关闭数据库
    def Close_DB(self):
        """
        dbManager._close_db()
        :return:
        """
        self.__cursor.close()
        self.__connect.close()
        # print('关闭连接')
    # 向head_and_food表插入数据
    def insert(self, head_food_lst):
        # 打开数据库连接
        db = self.__connect
        # 使用cursor()方法获取操作游标
        cursor = self.__cursor
        # SQL 插入语句
        sql = """INSERT INTO head_and_food(head_px, head_py, food_px, 
                    food_py, result) VALUES (%s,%s,%s,%s,%s)"""
        try:
            # 执行sql语句
            cursor.execute(sql, head_food_lst)
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            db.rollback()
        return db

    # 查询head_and_food表的所有数据
    def select_all(self):
        # 打开数据库连接
        db = self.__connect
        # 使用cursor()方法获取操作游标
        cursor = self.__cursor
        # SQL 查询语句
        sql = "SELECT * FROM head_and_food"
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            return results
        except:
            print("Error: unable to fetch data")
            return None


if __name__ == '__main__':
    food_and_snakenode = Food_and_snakenode('software_test', 'root', 'Root@1234')
    food_and_snakenode.connect_db()
    results = food_and_snakenode.select_all()
    print(results)
    food_and_snakenode.Close_DB()